﻿// ===============================================================================
// 浪潮GSP平台
// SQLServer数据库下的查询SQL语句
// 请查看《GSP7-数据访问引擎子系统概要设计说明书》来了解关于此类的更多信息。
// ===============================================================================
// 变更历史纪录
// 时间			             版本	    修改人	        描述
// 2013/5/24 13:39:26        1.0        周国庆           初稿。
// ===============================================================================
// 开发者: 周国庆
// 2013/5/24 13:39:26 
// (C) 2013 Genersoft Corporation 版权所有
// 保留所有权利。
// ===============================================================================

using System;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Generic;

namespace T.FAS.Runtime.DataAccess.CDOEngine
{
    /// <summary>
    /// SQLServer数据库下的查询SQL语句
    /// </summary>
    /// <remarks> SQLServer数据库下的查询SQL语句</remarks>    
    [Serializable]
    internal class SelectSqlStaForMSS : SelectSqlStatement
    {

        #region 字段
        private DBCustomTools dBCustomTools = new DBCustomTools(DBCustomTools.DB_MSSQL);
        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        public SelectSqlStaForMSS()
            : base() { }

        #endregion

        #region 方法

        /// <summary>
        /// 转换成SQL语句
        /// </summary>
        /// <returns>Select SQL语句</returns>
        public override string ToSQL()
        {
            if (PageCount == 0)
            {
                return base.ToSQL();
            }
            else
            {
                //判断元数据是否被签出
                var stringBuilder = new StringBuilder();
                var orderbyCondition = OrderByCondition.ToSQL();
                var rowNumber = string.Format("ROW_NUMBER() Over({0}) as row_num", orderbyCondition);
                stringBuilder.Append(string.Format("SELECT * FROM (SELECT {0},{1} FROM {2}", SelectList.ToSQL(), rowNumber, From.ToSQL() + " with(nolock) "));
                foreach (SqlElement element in MainFromItem.ChildCollection)
                {
                    string joinItemSQL = string.Empty;
                    if (element is InnerJoinItem innerJoinItem)
                    {
                        if (innerJoinItem.IsExtendItem)
                        {
                            joinItemSQL = innerJoinItem.ToSQLEx();
                        }
                        else
                        {
                            joinItemSQL = innerJoinItem.ToSQL();
                        }
                    }
                    else if (element is LeftJoinItem leftJoinItem)
                    {
                        joinItemSQL = leftJoinItem.ToSQL();
                    }
                    joinItemSQL = dBCustomTools.Format(joinItemSQL);
                    stringBuilder.Append(joinItemSQL);
                }


                var joinCondition = JoinCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(joinCondition))
                {
                    stringBuilder.AppendFormat("WHERE {0} ", joinCondition);
                }

                var filterCondition = FilterCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(filterCondition))
                {
                    if (string.IsNullOrWhiteSpace(joinCondition))
                    {
                        stringBuilder.AppendFormat(" WHERE {0} ", filterCondition);
                    }
                    else
                    {
                        stringBuilder.AppendFormat(" AND {0} ", filterCondition);
                    }
                }
                if (PageCount > 0)
                {
                    stringBuilder.AppendFormat(") as tabledata WHERE row_num BETWEEN ({0}-1)*{1}+1 AND ({0}-1)*{1}+{1}", PageIndex, PageCount);
                    //if (string.IsNullOrWhiteSpace(joinCondition) && string.IsNullOrWhiteSpace(filterCondition))
                    //{
                    //    stringBuilder.AppendFormat(") as tabledata WHERE row_num BETWEEN ({0}-1)*{1}+1 AND ({0}-1)*{1}+{1}", PageIndex, PageCount);
                    //}
                    //else
                    //{
                    //    stringBuilder.AppendFormat(" ) as tabledata row_num BETWEEN ({0}-1)*{1}+1 AND ({0}-1)*{1}+{1}", PageIndex, PageCount);
                    //}
                }

                //if (!string.IsNullOrWhiteSpace(orderbyCondition))
                //{
                //    stringBuilder.AppendFormat(" ORDER BY {0}", orderbyCondition);
                //}
                string querySQL = stringBuilder.ToString();
                string countSQL = GetPagingSQL();
                return querySQL.EndsWith(";") ? querySQL + countSQL : querySQL + ";" + countSQL;
            }
        }

        /// <summary>
        /// 转换成SQL语句
        /// </summary>
        /// <returns>Select SQL语句</returns>
        private string GetPagingSQL()
        {
            if (PageCount == 0)
            {
                return base.ToSQL();
            }
            else
            {
                //判断元数据是否被签出
                var stringBuilder_PageCount = new StringBuilder();
                stringBuilder_PageCount.AppendFormat(" select count(*) as _totalCount from {0}", From.ToSQL() + " with(nolock) ");
                foreach (SqlElement element in MainFromItem.ChildCollection)
                {
                    string joinItemSQL = string.Empty;
                    if (element is InnerJoinItem innerJoinItem)
                    {
                        if (innerJoinItem.IsExtendItem)
                        {
                            joinItemSQL = innerJoinItem.ToSQLEx();
                        }
                        else
                        {
                            joinItemSQL = innerJoinItem.ToSQL();
                        }
                    }
                    else if (element is LeftJoinItem leftJoinItem)
                    {
                        joinItemSQL = leftJoinItem.ToSQL();
                    }
                    joinItemSQL = dBCustomTools.Format(joinItemSQL);
                    stringBuilder_PageCount.Append(joinItemSQL);
                }

                var joinCondition = JoinCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(joinCondition))
                {
                    stringBuilder_PageCount.AppendFormat("WHERE {0} ", joinCondition);
                }

                var filterCondition = FilterCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(filterCondition))
                {
                    if (string.IsNullOrWhiteSpace(joinCondition))
                    {
                        stringBuilder_PageCount.AppendFormat(" WHERE {0} ", filterCondition);
                    }
                    else
                    {
                        stringBuilder_PageCount.AppendFormat(" AND {0} ", filterCondition);
                    }
                }

                string countSQL = stringBuilder_PageCount.ToString();
                return countSQL;
            }
        }

        #endregion
    }

    public class DBCustomTools
    {
        public const string DB_MySQL = "mysql";
        public const string DB_MSSQL = "mssql";
        /// <summary>
        /// 占位符
        /// </summary>
        public readonly static string table_hint_placeholder = "{0}";
        public string CurrentDB { get; private set; }

        public DBCustomTools(object instance)
        {
            if (instance is SelectSqlStaForMSS)
                CurrentDB = DB_MSSQL;
            else if (instance is SelectSqlStaForMySQL)
                CurrentDB = DB_MySQL;
            else if (instance is string DB)
            {
                switch (DB)
                {
                    case DB_MSSQL:
                        CurrentDB = DB_MSSQL;
                        break;
                    case DB_MySQL:
                        CurrentDB = DB_MySQL;
                        break;
                    default:
                        throw new Exception("DBCustomTools不支持的初始化DB类型字符串！");
                }
            }
            else
                throw new Exception("不支持的SelectSqlStaement子类！");
        }

        public string Format(string sqlElement)
        {
            return Format_WithNolock(sqlElement);
        }

        private string Format_WithNolock(string sqlElement)
        {
            if (CurrentDB == DB_MSSQL)
            {
                return string.Format(sqlElement, "with(nolock)");
            }
            else
            {
                return string.Format(sqlElement, "");
            }
        }

        public string AppendSQL()
        {
            return Append_WithNolock();
        }

        private string Append_WithNolock()
        {
            if (CurrentDB == DB_MSSQL)
            {
                return " with(nolock) ";
            }
            else
            {
                return "";
            }
        }
    }
}
